using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for PlaceDAO
/// </summary>
public class PlaceDAO:DBAccess
{
	 #region sql Commands   
    string SQL_SELECT_ALL = "select * from Place";
    string SQL_SELECT_BY_ID = "select * from Place where ID=@id";   
    
    string SQL_INSERT = "insert into Place(Name,TypeID,Description,ImageURL,Article,IsRecommended,ModifiedDate,ModifiedBy,State,LocationID, Rank)values(@Name,@TypeId,@Description,@ImageURL,@Article,@IsRecommended,@ModifiedDate,@ModifiedBy,@State,@LocationID,@rank)";
    string SQL_UPDATE = "update Place set Name = @Name, TypeId = @TypeId,Description= @Description,ImageURL=@ImageURL ,Article=@Article,IsRecommended=@IsRecommended,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy,State=@State,LocationID=@LocationId,Rank=@rank where ID = @id";
    string SQL_DELETE = "delete from Place where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public PlaceDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get place by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Place GetPlace(int id)
    {
        Place pla = new Place();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    pla.Id = dr.GetInt32(0);
                    pla.Name = dr.GetString(1);
                    pla.Category.Id= dr.GetInt32(2);                   
                    pla.Description = dr.GetString(3);
                    pla.ImageURL = dr.GetString(4);                   
                    pla.Article = dr.GetString(5);
                    pla.Rank = dr.GetInt32(6);
                    pla.IsRecommended = dr.GetBoolean(7);
                    pla.State = dr.GetBoolean(8);
                    pla.ModifiedDate = dr.GetDateTime(9);
                    pla.ModifiedBy = dr.GetInt32(10);
                    pla.Location.Id = dr.GetInt32(11);
                    
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return pla;
    }
    /// <summary>
    /// Get all places 
    /// </summary>
    /// <returns></returns>
    public IList<Place> GetAllPlaces()
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pla = new Place();
                    pla.Id = dr.GetInt32(0);
                    pla.Name = dr.GetString(1);
                    pla.Category.Id = dr.GetInt32(2);
                    pla.Description = dr.GetString(3);
                    pla.ImageURL = dr.GetString(4);
                    pla.Article = dr.GetString(5);
                    pla.Tag = dr.GetString(6);
                    pla.IsRecommended = dr.GetBoolean(7);
                    pla.ModifiedDate = dr.GetDateTime(8);
                    pla.ModifiedBy = dr.GetInt32(9);
                    pla.State = dr.GetBoolean(10);
                    list.Add(pla);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Insert new place into database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Insert(Place pla)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Name", pla.Name);
            command.Parameters.AddWithValue("@TypeId", pla.Category.Id);           
            command.Parameters.AddWithValue("@Description", pla.Description);
            command.Parameters.AddWithValue("@ImageURL", pla.ImageURL);           
            command.Parameters.AddWithValue("@Article", pla.Article);           
      
            command.Parameters.AddWithValue("@IsRecommended", pla.IsRecommended);
            command.Parameters.AddWithValue("@ModifiedDate", pla.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", pla.ModifiedBy);
            command.Parameters.AddWithValue("@State", pla.State);
            command.Parameters.AddWithValue("@LocationID",pla.Location.Id);
            command.Parameters.AddWithValue("@Rank", pla.Rank);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "SELECT @@IDENTITY from Place";
                pla.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update place
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Update(Place pla)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Name", pla.Name);
            command.Parameters.AddWithValue("@TypeId", pla.Category.Id);
            command.Parameters.AddWithValue("@Description", pla.Description);
            command.Parameters.AddWithValue("@ImageURL", pla.ImageURL);
            command.Parameters.AddWithValue("@Article", pla.Article);
            command.Parameters.AddWithValue("@IsRecommended", pla.IsRecommended);
            command.Parameters.AddWithValue("@ModifiedDate", pla.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", pla.ModifiedBy);
            command.Parameters.AddWithValue("@State", pla.State);
            command.Parameters.AddWithValue("@LocationId", pla.Location.Id);
            command.Parameters.AddWithValue("@Rank", pla.Rank);
            command.Parameters.AddWithValue("@id",pla.Id);  
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Remove place out of database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Delete(int id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Get place list with paging 
    /// </summary>
    /// <param name="currentPage">current page to view</param>
    /// <param name="pageSize">number of items per page</param>
    /// <param name="total">out parameter: totals record in query result </param>
    /// <param name="keyword">text to search</param>
    /// <returns></returns>
    public IList<Place> GetPlaces(int currentPage, int pageSize, ref int total,byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPlaces", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);   
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);
                    pl.Category.Name = dr.GetString(6);
                    pl.State = dr.GetBoolean(7);
                    pl.Rank = dr.GetInt32(8);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Get place list with paging, order, and condition
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="keyword"></param>
    /// <returns></returns>
    public IList<Place> GetPlacesExt(int currentPage, int pageSize, ref int total, string keyword, bool order, byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPlacesExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);

            command.Parameters.Add("@keyword",SqlDbType.NVarChar);
            command.Parameters.Add("@orderByRank", SqlDbType.Bit);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
          
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@orderByRank"].Value = order;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);
                    pl.Category.Name = dr.GetString(6);
                    pl.State = dr.GetBoolean(7);
                    pl.Rank = dr.GetInt32(8);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }

    public IList<Place> GetPlacesExtByLoc(int currentPage, int pageSize, ref int total, int cateId,int locationId, string keyword, bool order, byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPlacesExtByLoc", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@cateId", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@orderByRank", SqlDbType.Bit);
            command.Parameters.Add("@locationId", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@cateId"].Value = cateId;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@orderByRank"].Value = order;
            command.Parameters["@locationId"].Value = locationId;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);
                    pl.Category.Name = dr.GetString(6);
                    pl.State = dr.GetBoolean(7);
                    pl.Rank = dr.GetInt32(8);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }

    public IList<Place> GetPlacesExtWithRank(int currentPage, int pageSize, ref int total, int cateId, int locationId, string keyword, int rank, byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPlacesExtWithRank", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@cateId", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@rank", SqlDbType.Int);
            command.Parameters.Add("@locationId", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@cateId"].Value = cateId;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@rank"].Value = rank;
            command.Parameters["@locationId"].Value = locationId;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);
                    pl.Category.Name = dr.GetString(6);
                    pl.State = dr.GetBoolean(7);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public IList<Place> GetPlacesByCategory(int currentPage, int pageSize, ref int total,int cateId,string keyword, byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPlacesByCategory", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@cate_id", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@cate_id"].Value = cateId;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);                   
                    pl.State = dr.GetBoolean(6);
                    pl.Rank = dr.GetInt32(7);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<Place> GetRecommendedPlaces(int currentPage, int pageSize, ref int total, bool isRecommended, byte state)
    {
        IList<Place> list = new List<Place>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectRecommendedPlaces", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@recommended", SqlDbType.Bit);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@recommended"].Value = isRecommended;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Place pl = new Place();
                    pl.Id = dr.GetInt32(1);
                    pl.Name = dr.GetString(2);
                    pl.Description = dr.GetString(3);
                    pl.ImageURL = dr.GetString(4);
                    pl.Category.Id = dr.GetInt32(5);                   
                    pl.State = dr.GetBoolean(6);
                    pl.Rank = dr.GetInt32(7);
                    list.Add(pl);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    #endregion
}
